﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Xml;
using System.Data;
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.Diagnostics;
using FirebirdSql.Data.FirebirdClient;
namespace FBProductsSynchronization
{
    public class DataUtility
    {
        int i = 0;
        SqlDataReader DataReader = null;
        FbDataReader DataReaderfb = null;
        string VirginConnection = "user id=dinacorporate_admin;" + "password=pas5w0rd;server=122.201.105.167\\SQLSERVER;" + "database=dinacorporate_db; " + "connection timeout=800;" + "Max Pool Size = 800";

        string FireBirdConnection = "User ID=gone;Password=fishing;" +
                "Database=192.168.1.20:C:\\Documents and Settings\\Administrator\\Desktop\\DINA CORPORATE SETUP.FDB; " +
                "DataSource=192.168.1.20;Charset=NONE;" + "connection timeout=700;" + "Max Pool Size = 700";
        //string FireBirdConnection = "User ID=gone;Password=fishing;" +
        //      "Database=192.168.1.90:C:\\Program Files (x86)\\Fishbowl\\database\\data\\DINA CORPORATE SETUP.FDB; " +
        //      "DataSource=192.168.1.90;Charset=NONE;" + "connection timeout=800;" + "Max Pool Size = 800";

        //Input SQL command and return a data reader Modified:24/07/2012
        public FbDataReader returnDatareadfb(string SqlCommand)
        {
            try
            {
                FbConnection firebirdConnection = new FbConnection(FireBirdConnection);
                firebirdConnection.Open();
                FbCommand myCommand = new FbCommand(SqlCommand, firebirdConnection);
                DataReaderfb = myCommand.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return DataReaderfb;
        }

        public SqlDataReader returnDataread(string SqlCommand)
        {
            SqlConnection myConnection = new SqlConnection(VirginConnection);
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand(SqlCommand, myConnection);
            DataReader = myCommand.ExecuteReader();
            //myConnection.Close();
            return DataReader;
        }
        //Update order_item_delivery_fb Table for imported delivery ID
        public void UpdateFbTable(int deliveryId, int imported)
        {
            String InsertFbTable = "INSERT INTO order_item_delivery_fb_tbl (order_item_delivery_id ,order_item_delivery_fb_imported) " +
                                   "VALUES(" + deliveryId + "," + imported + ")";
            SqlConnection myConnection = new SqlConnection(VirginConnection);
            myConnection.Open();
            SqlCommand myCommand = new SqlCommand(InsertFbTable, myConnection);
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
        public string FormatDate(string tmpDate)
        {
            return DateTime.Parse(tmpDate).ToString("yyyy-MM-ddTHH:mm:ss");
        }
     
        //Return a ship list of a sale order

        public void ExportProducts(string exportType, string stockName)
        {
            DateTime startDate = DateTime.Now;
            Console.WriteLine("Start sync products from fishbowl " + startDate + " .........");
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            string productNum;
            string productCategory;
            string NextPurchase;
            string upc;
            string PartID;
            string desc;
            string price;
            string productdatedate;
            string nextpurchase;
            int parentId;
            bool flag;
            SqlCommand command;

            try
            {
                virginConnection.Open();
                string readCommand = "select PARTID,UPC,DATELASTMODIFIED,NUM,product.DESCRIPTION,PRICE, PRODUCTTREE.NAME,ActiveFlag,PRODUCTTREE.id,parentid from product left join PRODUCTTOTREE on PRODUCTTOTREE.PRODUCTID = PRODUCT.ID  LEFT JOIN PRODUCTTREE ON PRODUCTTREE.ID = PRODUCTTOTREE.PRODUCTTREEID where Num = 'BELT30mmM - BLK - S'";
                FbDataReader myreader = returnDatareadfb(readCommand);
                //Count if Product is added to database. Return Zero 0 if empty
                int j = 0;
                int productTreeId;
                FbConnection firebirdConnection = new FbConnection(FireBirdConnection);
                firebirdConnection.Open();
                while (myreader.Read())
                {
                    productNum = myreader[3].ToString();
                    productCategory = myreader[6].ToString();
                    Console.WriteLine(productNum);

                    upc = myreader[1].ToString();
                    PartID = myreader[0].ToString();
                    desc = myreader[4].ToString();
                    NextPurchase = NextPurchaseDate(PartID);
                    parentId = int.Parse(myreader[9].ToString());
                    if (myreader[8].ToString() != "" && parentId !=1)
                    {
                        productTreeId = int.Parse(myreader[8].ToString());
                        price = PricingRule(productTreeId, ParentName(parentId));
                        flag = existProductNum(productNum.Replace("'", "''"), price, ParentName(parentId) + ":" + productCategory);
                    }
                    else {
                        productTreeId = 1;
                        price = myreader[5].ToString();
                        flag = existProductNum(productNum.Replace("'", "''"), price, productCategory);
                    }

                    //NextPurchase = "";
                    nextpurchase = NextPurchase;
                   
                    if (flag== false && productNum != "123Dummy Product321")
                    {
                        productdatedate = DateTime.Parse(myreader[2].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                        // We have to know the name of the company ex: Product:Virgin
                        if ((productCategory == stockName || stockName == "All"))
                        {

                            command = new SqlCommand("SP_FB_Products_Insert", virginConnection);
                            command.CommandType = CommandType.StoredProcedure;
                            command.Parameters.Add(new SqlParameter("@Product_PartID", SqlDbType.Int, 0, "Product_PartID"));
                            command.Parameters.Add(new SqlParameter("@Product_Upc", SqlDbType.NVarChar, 150, "Product_Upc"));
                            command.Parameters.Add(new SqlParameter("@Product_Num", SqlDbType.NVarChar, 50, "Product_Num"));
                            command.Parameters.Add(new SqlParameter("@Product_Desc", SqlDbType.NVarChar, 50, "Product_Desc"));
                            command.Parameters.Add(new SqlParameter("@Product_Price", SqlDbType.Float, 0, "Product_Price"));
                            command.Parameters.Add(new SqlParameter("@Product_Date", SqlDbType.DateTime, 0, "Product_Date"));
                            command.Parameters.Add(new SqlParameter("@Product_NextPurchase", SqlDbType.NVarChar, 50, "Product_NextPurchase"));
                            command.Parameters.Add(new SqlParameter("@Product_ActiveFlag", SqlDbType.SmallInt, 0, "Product_ActiveFlag"));

                            command.Parameters[0].Value = PartID;
                            command.Parameters[1].Value = upc;
                            command.Parameters[2].Value = productNum;
                            command.Parameters[3].Value = desc;
                            command.Parameters[4].Value = price;

                            //Datetime from Fishbowl always includes T so we have to remove T
                            command.Parameters[5].Value = DateTime.Parse(myreader[2].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                            if (NextPurchase != "")
                                command.Parameters[6].Value = NextPurchase;
                            else
                                command.Parameters[6].Value = DBNull.Value;
                            command.Parameters[7].Value = myreader[7].ToString();
                            command.ExecuteNonQuery();
                            //Find Product Id
                            int productId = FindProductId(productNum, price);

                            AddProductTree(productId, productCategory, virginConnection, parentId);
                            //Add stock here
                            AddStock2(productNum, PartID, productId, virginConnection, firebirdConnection);

                            j = j + 1;
                        }
                    }

                    else if (flag == true && productNum != "123Dummy Product321")
                    {
                        command = new SqlCommand("SP_FB_Products_update", virginConnection);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@Product_PartID", SqlDbType.Int, 0, "Product_PartID"));
                        command.Parameters.Add(new SqlParameter("@Product_Upc", SqlDbType.NVarChar, 150, "Product_Upc"));
                        command.Parameters.Add(new SqlParameter("@Product_Num", SqlDbType.NVarChar, 50, "Product_Num"));
                        command.Parameters.Add(new SqlParameter("@Product_Desc", SqlDbType.NVarChar, 50, "Product_Desc"));
                        command.Parameters.Add(new SqlParameter("@Product_Price", SqlDbType.Float, 0, "Product_Price"));
                        command.Parameters.Add(new SqlParameter("@Product_Date", SqlDbType.DateTime, 0, "Product_Date"));
                        command.Parameters.Add(new SqlParameter("@Product_NextPurchase", SqlDbType.NVarChar, 50, "Product_NextPurchase"));
                        command.Parameters.Add(new SqlParameter("@Product_ActiveFlag", SqlDbType.SmallInt, 0, "Product_ActiveFlag"));

                        command.Parameters[0].Value = PartID;
                        command.Parameters[1].Value = upc;
                        command.Parameters[2].Value = productNum;
                        command.Parameters[3].Value = desc;
                        command.Parameters[4].Value = price;

                        //Datetime from Fishbowl always includes T so we have to remove T
                        command.Parameters[5].Value = DateTime.Parse(myreader[2].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                        if (NextPurchase != "")
                            command.Parameters[6].Value = NextPurchase;
                        else
                            command.Parameters[6].Value = DBNull.Value;
                        command.Parameters[7].Value = myreader[7].ToString();
                        command.ExecuteNonQuery();
                        j = j + 1;
                        int productId = FindProductId(productNum,price);
                        AddProductTree(productId, productCategory, virginConnection, parentId);
                        AddStock2(productNum, PartID, productId, virginConnection, firebirdConnection);
                    }
                    
                }

                myreader.Close();
                //QB_Item_Update(virginConnection);
                virginConnection.Close();

                Console.WriteLine(j + " Products have been sync");
                Console.WriteLine("Finish sync products from fishbowl to database at " + DateTime.Now);
                firebirdConnection.Close();
                DateTime endDate = DateTime.Now;
                Sync_Tracking_Insert(startDate, endDate, "sync " + j + " products");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                sendEmail(e.Message.ToString() + " " + stockName);
            }
        }

        //Check if Product Number exist in the database, if exis return true otherwise return false
        public bool existProductNum(string productNum, string productPrice, string productTree)
        {
            bool flag = false;
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            decimal price = decimal.Parse(productPrice) * 1.1M;
            try
            {
                virginConnection.Open();
                string sql = "select * from FB_item_tbl left join FB_item_tree_tbl on FB_item_tree_tbl.product_id = FB_item_tbl.product_id "+
                "where Product_Num = '" + productNum + "' and product_price=" + Math.Round(price, 2) + " " +
                    "and  Product_Tree_Name='" + productTree+"'";
                SqlCommand command = new SqlCommand(sql, virginConnection);
                object result = command.ExecuteScalar();
                if (result != null)
                    flag = true;
                else
                    flag = false;
                virginConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return flag;
        }
        //Check if product exist in product tree tbl
        public bool existProductTree(int productId, SqlConnection virginConnection)
        {
            bool flag = false;
            try
            {

                string sql = "select * from FB_item_tree_tbl where Product_ID = '" + productId + "'";
                SqlCommand command = new SqlCommand(sql, virginConnection);
                object result = command.ExecuteScalar();
                if (result != null)
                    flag = true;
                else
                    flag = false;


            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return flag;
        }
        //Add product tree of each product to FB_item_tree_tbl
        public void AddProductTree(int productId, string productCategory, SqlConnection virginConnection, int productTreeId)
        {
            string ParenNAME = ParentName(productTreeId);

            SqlCommand command2 = new SqlCommand("SP_FB_item_tree_Insert", virginConnection);
            command2.CommandType = CommandType.StoredProcedure;
            command2.Parameters.Add(new SqlParameter("@Product_ID", SqlDbType.Int, 0, "Product_ID"));
            command2.Parameters.Add(new SqlParameter("@Product_Tree_Name", SqlDbType.NVarChar, 250, "Product_Tree_Name"));
            command2.Parameters[0].Value = productId;
            if (productTreeId == 1)
            command2.Parameters[1].Value = productCategory;
            else
                command2.Parameters[1].Value = ParenNAME+":"+productCategory;
            command2.ExecuteNonQuery();

        }
        public string ParentName (int productTreeId)
        {
            string parentName = "";
            string readCommand = "select name from PRODUCtTREE where id=" + productTreeId;
            FbConnection firebirdConnection = new FbConnection(FireBirdConnection);
            firebirdConnection.Open();
            FbCommand myCommand = new FbCommand(readCommand, firebirdConnection);
            DataReaderfb = myCommand.ExecuteReader();
            while(DataReaderfb.Read())
            {
                parentName = DataReaderfb[0].ToString();
            }
            firebirdConnection.Close();
            return parentName;

        }
        public string PricingRule(int productTreeId, string subProductTree)
        {
            string price = "";
            string readCommand = "select PAAMOUNT from PRICINGRULE left join  PRODUCtTREE  on PRICINGRULE.PRODUCTINCLID = PRODUCtTREE.id "+
"where PRODUCtTREE.id = " + productTreeId ;
            FbConnection firebirdConnection = new FbConnection(FireBirdConnection);
            firebirdConnection.Open();
            FbCommand myCommand = new FbCommand(readCommand, firebirdConnection);
            DataReaderfb = myCommand.ExecuteReader();
            while (DataReaderfb.Read())
            {
                price = DataReaderfb[0].ToString();
            }
            return price;
        }
        public bool existStock(int productId, string location, string quantity)
        {
            bool flag = false;
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            try
            {
                virginConnection.Open();
                string sql = "select * from FB_item_stock_tbl where Product_ID = '" + productId + "' and Stock_location='" + location + "'";
                SqlCommand command = new SqlCommand(sql, virginConnection);
                object result = command.ExecuteScalar();
                if (result != null)
                    flag = true;
                else
                    flag = false;
                virginConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return flag;
        }

        //Add stock from FB to database(Stock_FB_tbl)

        public void AddStock1(ConnectionObject connectionObject, string key, string ProductNum, int productId, SqlConnection virginConnection)
        {
            try
            {
                string returnInvt = connectionObject.sendCommand(Fishbowl.InvQtyRq(key, ProductNum));
                //Console.WriteLine(returnInvt);
                XDocument rowlist = XDocument.Parse(returnInvt);
                SqlCommand command1;
                string product;
                string stockLocation;
                string stockQuantity;
                int j = 0;
                foreach (XElement element in rowlist.Descendants("InvQty"))
                {
                    // Console.WriteLine(element.Element("Location").Element("LocationGroupName").Value);
                    // Console.WriteLine(element.Element("QtyOnHand").Value);

                    product = ProductNum;
                    stockLocation = element.Element("Location").Element("LocationGroupName").Value;
                    stockQuantity = element.Element("QtyOnHand").Value;

                    Boolean stockcheck = existStock(productId, stockLocation, stockQuantity);
                    if (stockcheck == true && product != "PartNumber")
                    {

                        command1 = new SqlCommand("SP_update_Stock_FB", virginConnection);
                        command1.CommandType = CommandType.StoredProcedure;
                        command1.Parameters.Add(new SqlParameter("@Product_ID", SqlDbType.Int, 0, "Product_ID"));
                        command1.Parameters.Add(new SqlParameter("@Stock_location", SqlDbType.NVarChar, 150, "Stock_location"));
                        command1.Parameters.Add(new SqlParameter("@Stock_Quantity", SqlDbType.Int, 0, "Stock_Quantity"));
                        command1.Parameters[0].Value = productId;
                        command1.Parameters[1].Value = stockLocation;
                        command1.Parameters[2].Value = int.Parse(stockQuantity.ToString());
                        command1.ExecuteNonQuery();

                    }
                    else if (stockcheck == false && product != "PartNumber")
                    {

                        command1 = new SqlCommand("SP_FB_Stock_Insert", virginConnection);
                        command1.CommandType = CommandType.StoredProcedure;
                        command1.Parameters.Add(new SqlParameter("@Product_ID", SqlDbType.NVarChar, 50, "Product_ID"));
                        command1.Parameters.Add(new SqlParameter("@Stock_location", SqlDbType.NVarChar, 150, "Stock_location"));
                        command1.Parameters.Add(new SqlParameter("@Stock_Quantity", SqlDbType.Int, 0, "Stock_Quantity"));
                        command1.Parameters[0].Value = productId;
                        command1.Parameters[1].Value = stockLocation;
                        command1.Parameters[2].Value = int.Parse(stockQuantity.ToString());
                        command1.ExecuteNonQuery();
                    }
                    j++;
                }
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                sendEmail(e.Message.ToString() + " " + ProductNum);
            }

        }
        public void AddStock2(string ProductNum, string PartId, int productId, SqlConnection virginConnection, FbConnection firebirdConnection)
        {
            try
            {
                string product;
                string stockLocation;
                string stockQuantity;
                int j = 0;
                SqlCommand command1;
                FbConnection qtyonhandconnection = new FbConnection(FireBirdConnection);

                qtyonhandconnection.Open();

                string sql = "SELECT tag.partid,location.locationgroupid,COALESCE(SUM(qty),0) AS qty FROM Tag INNER JOIN Location ON Location.ID = Tag.locationID WHERE Tag.typeID IN (30, 40)" + "and partid = " + int.Parse(PartId) + " GROUP BY Location.locationGroupID, tag.partid";
                FbCommand readCommand = new FbCommand(sql, qtyonhandconnection);
                //FbDataReader myreader1 = returnDatareadfb(sql);
                FbDataReader myreader1 = readCommand.ExecuteReader();
                while (myreader1.Read())
                {
                    if (myreader1[1].ToString() != "" && myreader1[2].ToString() != "")
                    {
                        product = ProductNum;
                        stockLocation = returnGroupLocation(int.Parse(myreader1[1].ToString()));
                        stockQuantity = myreader1[2].ToString();

                        Boolean stockcheck = existStock(productId, stockLocation, stockQuantity);
                        if (stockcheck == true && product != "PartNumber")
                        {
                            command1 = new SqlCommand("SP_update_Stock_FB", virginConnection);
                            command1.CommandType = CommandType.StoredProcedure;
                            command1.Parameters.Add(new SqlParameter("@Product_ID", SqlDbType.Int, 0, "Product_ID"));
                            command1.Parameters.Add(new SqlParameter("@Stock_location", SqlDbType.NVarChar, 150, "Stock_location"));
                            command1.Parameters.Add(new SqlParameter("@Stock_Quantity", SqlDbType.Int, 0, "Stock_Quantity"));
                            command1.Parameters[0].Value = productId;
                            command1.Parameters[1].Value = stockLocation;
                            int quantity;
                            int.TryParse(stockQuantity.ToString(), out quantity);
                            command1.Parameters[2].Value = quantity;
                            command1.ExecuteNonQuery();

                        }
                        else if (stockcheck == false && product != "PartNumber")
                        {
                            command1 = new SqlCommand("SP_FB_Stock_Insert", virginConnection);
                            command1.CommandType = CommandType.StoredProcedure;
                            command1.Parameters.Add(new SqlParameter("@Product_ID", SqlDbType.NVarChar, 50, "Product_ID"));
                            command1.Parameters.Add(new SqlParameter("@Stock_location", SqlDbType.NVarChar, 150, "Stock_location"));
                            command1.Parameters.Add(new SqlParameter("@Stock_Quantity", SqlDbType.Int, 0, "Stock_Quantity"));
                            command1.Parameters[0].Value = productId;
                            command1.Parameters[1].Value = stockLocation;
                            int quantity;
                            int.TryParse(stockQuantity.ToString(), out quantity);
                            command1.Parameters[2].Value = quantity;
                            command1.ExecuteNonQuery();
                        }
                        j++;
                    }
                }
                qtyonhandconnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
                sendEmail(e.Message.ToString() + " " + ProductNum);
            }

        }
        public string returnGroupLocation(int id)
        {
            string groupLocation = "";
            try
            {
                string sql = "select name from LOCATIONGROUP where id = " + id;
                FbConnection firebirdConnection = new FbConnection(FireBirdConnection);
                FbCommand readCommand = new FbCommand(sql, firebirdConnection);

                firebirdConnection.Open();

                FbDataReader result = readCommand.ExecuteReader();
                while (result.Read())
                {
                    if (result["name"].ToString() != "")
                        groupLocation = result["name"].ToString();
                }
                firebirdConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return groupLocation;
        }

        //Return next purchase date, it returns a string because FB return date in 07/08/12
        public string NextPurchaseDate(string partId)
        {
            string dateSchedule = "";
            try
            {

                string sql = "select PARTNUM,DATEISSUED,DATESCHEDULEDFULFILLMENT  from poitem left join po on po.id=poitem.poid  where  " +
                " poitem.typeid= 10 and (poitem.statusid = 10 or poitem.statusid = 20 or poitem.statusid = 30 or poitem.statusid = 40 ) " +
" and partid = " + int.Parse(partId) + " and (DATEISSUED >= '" + DateTime.Now.ToString("yyyy-MM-dd") + "' or DATESCHEDULEDFULFILLMENT >='" + DateTime.Now.ToString("yyyy-MM-dd") + "')";
                FbConnection firebirdConnection = new FbConnection(FireBirdConnection);
                FbCommand readCommand = new FbCommand(sql, firebirdConnection);

                firebirdConnection.Open();

                FbDataReader result = readCommand.ExecuteReader();
                while (result.Read())
                {
                    if (result["PARTNUM"].ToString() != "" && (result["DATEISSUED"].ToString() != "" || result["DATESCHEDULEDFULFILLMENT"].ToString()!= ""))
                    {
                        if (DateTime.Parse(result["DATEISSUED"].ToString()) >= DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")))
                            dateSchedule = DateTime.Parse(result["DATEISSUED"].ToString()).ToString("yyyy-MM-dd hh:mm:ss");
                        else if (DateTime.Parse(result["DATESCHEDULEDFULFILLMENT"].ToString()) >= DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")))
                            dateSchedule = DateTime.Parse(result["DATESCHEDULEDFULFILLMENT"].ToString()).ToString("yyyy-MM-dd hh:mm:ss");
                    }
                }
                firebirdConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
            return dateSchedule;
        }

        public void Sync_Tracking_Insert(DateTime startDate, DateTime finishDate, string desctiption)
        {
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            try
            {
                virginConnection.Open();
                SqlCommand command = new SqlCommand("SP_FB_sync_tracking_Insert", virginConnection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@Date_start", SqlDbType.DateTime, 0, "Date_start"));
                command.Parameters.Add(new SqlParameter("@Date_finish", SqlDbType.DateTime, 0, "Date_finish"));
                command.Parameters.Add(new SqlParameter("@description", SqlDbType.NVarChar, 250, "description"));

                command.Parameters[0].Value = startDate;
                command.Parameters[1].Value = finishDate;
                command.Parameters[2].Value = desctiption;
                int y = command.ExecuteNonQuery();
                virginConnection.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
        }
        public int FindProductId(string productNum, string price)
        {
            int productId = 1;
            decimal productPrice = Math.Round(decimal.Parse(price)*1.1M,2);
            try
            {
                string sql = "select * from FB_item_tbl where Product_Num = '" + productNum + "' and product_price=" + productPrice;
                SqlDataReader result = returnDataread(sql);
                while (result.Read())
                {
                    if (result["Product_Num"].ToString() == productNum)
                        productId = int.Parse(result["Product_ID"].ToString());

                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

            return productId;
        }

        public static void sendEmail(string messageError)
        {

            var fromAddress = new MailAddress("system@essomenic.net", "From System");
            var toAddress = new MailAddress("khanhln@dinacorporate.com.au", "To admin");
            const string fromPassword = "aaabbb";

            var smtp = new SmtpClient
            {
                Host = "smtp.essomenic.net",
                Port = 25,
                EnableSsl = false,
                //DeliveryMethod = SmtpDeliveryMethod.Network,
                //UseDefaultCredentials = false,
                Credentials = new NetworkCredential(fromAddress.Address, fromPassword)
            };
            using (var message = new MailMessage(fromAddress, toAddress)
            {
                Subject = "Error FB Product Sync",
                Body = messageError
            })
            {
                smtp.Send(message);
            }
        }

        public void deleteNonExistProduct(ConnectionObject connectionObject, string key)
        {
            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            try
            {
                string sql = "select Product_Num from FB_item_tbl";
                SqlDataReader myReader = returnDataread(sql);
                virginConnection.Open();
                while (myReader.Read())
                {
                    string productNum = myReader["Product_Num"].ToString();
                    Console.WriteLine(productNum);
                    string message = returnProductMsg(connectionObject, key, productNum);
                    if (message == "2100")
                    {
                        SqlCommand command = new SqlCommand("SP_FB_item_delete", virginConnection);
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add(new SqlParameter("@Product_Num", SqlDbType.NVarChar, 150, "Product_Num"));
                        command.Parameters[0].Value = productNum;
                        command.ExecuteNonQuery();
                    }
                }
                virginConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

        }
        public string returnProductMsg(ConnectionObject connectionObject, string key, string productNum)
        {
            string message = "";
            string returnProduct = connectionObject.sendCommand(Fishbowl.ProductRQ(key, productNum));
            XDocument ProductMsg = XDocument.Parse(returnProduct);
            foreach (XElement element in ProductMsg.Descendants("ProductQueryRs"))
            {
                message = element.Attribute("statusCode").Value.ToString();
            }
            return message;
        }

        public void updateStock(ConnectionObject connectionObject, string key)
        {

            SqlConnection virginConnection = new SqlConnection(VirginConnection);
            try
            {
                int j = 0;
                DateTime startDate = DateTime.Now;
                Console.WriteLine("Start sync stock " + startDate);
                string sql = "select Product_Num,product_price Product_ID from FB_item_tbl";
                SqlDataReader myReader = returnDataread(sql);
                virginConnection.Open();
                while (myReader.Read())
                {
                    string productNum = myReader["Product_Num"].ToString();
                    string price = myReader["product_price"].ToString();
                    Console.WriteLine(productNum);
                    int productId = FindProductId(productNum, price);
                    AddStock1(connectionObject, key, productNum, productId, virginConnection);
                    j++;
                }
                virginConnection.Close();
                DateTime endDate = DateTime.Now;
                Sync_Tracking_Insert(startDate, endDate, "sync stock " + j + " product");
                Console.WriteLine("Finish sync stock " + endDate);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }
        }

        public void QB_Item_Update(SqlConnection virginConnection)
        {
            try
            {
                string sql = "select * from FB_item_tbl LEFT JOIN FB_item_tree_tbl on FB_item_tree_tbl.Product_ID = FB_item_tbl.Product_ID";
                //SqlConnection virginConnection = new SqlConnection(VirginConnection);
                SqlDataReader myReader2 = returnDataread(sql);
                while (myReader2.Read())
                {
                    if (myReader2["Product_Upc"].ToString() != "")
                    {
                    SqlCommand command = new SqlCommand("SP_FB_QBITEM", virginConnection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@ListID", SqlDbType.VarChar, 36, "ListID"));
                    command.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 31, "Name"));
                    command.Parameters.Add(new SqlParameter("@FullName", SqlDbType.VarChar, 209, "FullName"));
                    command.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 209, "@Description"));
                    command.Parameters.Add(new SqlParameter("@IsActive", SqlDbType.Bit, 0, "@IsActive"));
                    command.Parameters.Add(new SqlParameter("@ParentRefListID", SqlDbType.VarChar, 36, "@ParentRefListID"));
                    command.Parameters.Add(new SqlParameter("@ParentRefFullName", SqlDbType.VarChar, 209, "ParentRefFullName"));
                    command.Parameters.Add(new SqlParameter("@Sublevel", SqlDbType.Int, 0, "@Sublevel"));
                    command.Parameters.Add(new SqlParameter("@Type", SqlDbType.VarChar, 25, "@Type"));
                    command.Parameters.Add(new SqlParameter("@SalesDesc", SqlDbType.VarChar, 4095, "@SalesDesc"));
                    command.Parameters.Add(new SqlParameter("@SalesPrice", SqlDbType.Float, 0, "@SalesPrice"));
                    command.Parameters.Add(new SqlParameter("@PurchaseDesc", SqlDbType.VarChar, 4095, "@PurchaseDesc"));
                    command.Parameters.Add(new SqlParameter("@PurchaseCost", SqlDbType.Float, 0, "@PurchaseCost"));
                    command.Parameters.Add(new SqlParameter("@CustomFieldD_Barcode", SqlDbType.VarChar, 50, "@CustomFieldD_Barcode"));
                    command.Parameters.Add(new SqlParameter("@ExpectedDate", SqlDbType.DateTime, 0, "@ExpectedDate"));

                    command.Parameters[0].Value = myReader2["Product_ID"].ToString();
                    command.Parameters[1].Value = myReader2["Product_Num"].ToString();
                    command.Parameters[2].Value = myReader2["Product_Desc"].ToString();
                    command.Parameters[3].Value = myReader2["Product_Desc"].ToString();
                    command.Parameters[4].Value = int.Parse(myReader2["Product_ActiveFlag"].ToString());
                    command.Parameters[5].Value = DBNull.Value;
                    if (myReader2["Product_Tree_Name"].ToString().ToUpper() == "STOCKLAND")
                        command.Parameters[6].Value = "FG:Stocklands";
                    else if (myReader2["Product_Tree_Name"].ToString().ToUpper() == "NRMA WEB")
                        command.Parameters[6].Value = "FG:NRMA";
                    else
                    command.Parameters[6].Value = "FG:"+myReader2["Product_Tree_Name"].ToString().ToUpper();
                    command.Parameters[7].Value = 2;
                    command.Parameters[8].Value = "ItemInventory";
                    command.Parameters[9].Value = myReader2["Product_Desc"].ToString();
                    command.Parameters[10].Value = float.Parse(myReader2["Product_Price"].ToString());
                    command.Parameters[11].Value = myReader2["Product_Desc"].ToString();
                    command.Parameters[12].Value = 0;
                    command.Parameters[13].Value = myReader2["Product_Upc"].ToString();
                    if (myReader2["Product_NextPurchase"].ToString() == "")
                        command.Parameters[14].Value = DBNull.Value;
                    else
                        command.Parameters[14].Value = DateTime.Parse(myReader2["Product_NextPurchase"].ToString());
                    command.ExecuteNonQuery();
                    }
                }
                //virginConnection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message.ToString());
            }

        }
    }
}

